package renshi.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import org.apache.struts2.ServletActionContext;

import renshi.base.BaseDAO;
import renshi.model.Apply;

public class ApplyDAO extends BaseDAO {
	HttpServletRequest request = ServletActionContext.getRequest();
	HttpSession session = request.getSession();
	String cname=(String)session.getAttribute("cname");
	public ApplyDAO(Connection con) {
		super(con);
	}
	
	
	//��ѯ��ٵ���ϸ��Ϣ
	public List<Apply> queryall(int aid){
		String sql = "select * from Apply where aid='"+aid+"'";
		List<Apply> list1 = new ArrayList<Apply>();
		try{
			stmt=con.createStatement();
			rs=stmt.executeQuery(sql); 
			if(rs.next()){
				Apply apply=new Apply();
				apply.setAid(rs.getInt("aid"));
				apply.setNum(rs.getString("num"));
				apply.setBegintime(rs.getString("begintime"));
				apply.setEndtime(rs.getString("endtime"));
				apply.setReason(rs.getString("reason"));
				apply.setStatus(rs.getInt("status"));
				apply.setRemark(rs.getString("remark"));
				apply.setDays(rs.getInt("days"));
				list1.add(apply);
				return list1;
			}else{
				return null;
			}
		}
		catch(SQLException e){
			e.printStackTrace();
			return null;
		}
	}
	//�����ٵ�
	public boolean update(int status,int aid){
		String sql="update Apply set status='"+status+"' where aid='"+aid+"'";
		try{
			pstmt=con.prepareStatement(sql);
			int flag=pstmt.executeUpdate();
			if(flag!=0){
				return true;
			}else{
				return false;
			}
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}
	}
	//��ѯ�������ٵ�
	public List<Apply> query(){		
		String sql="select * from Apply where status=1 and days<4 and num in(select num from Student where cname='"+cname+"')";
		List<Apply> list0 = new ArrayList<Apply>();
		try{
			stmt=con.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next()){
				Apply apply=new Apply();
				apply.setAid(rs.getInt("aid"));
				apply.setNum(rs.getString("num"));			
				apply.setStatus(rs.getInt("status"));
				apply.setDays(rs.getInt("days"));
				list0.add(apply);;
			}
			return list0;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}
	}
	//��ѯ��ʷ��ٵ�
	public List<Apply> history(){
		String sql="select * from Apply where status!=1 and num in(select num from Student where cname='"+cname+"')";
		List<Apply> list2 = new ArrayList<Apply>();
		try{
			stmt=con.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next()){
				Apply apply=new Apply();
				apply.setNum(rs.getString("num"));
				apply.setStatus(rs.getInt("status"));
				apply.setAid(rs.getInt("aid"));
				apply.setDays(rs.getInt("days"));
				list2.add(apply);;
			}
			return list2;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}
	}
	//��ѯ���ѧ��
	public List<Apply> countstu(){
		String sql="select distinct(num) from Apply where num in(select num from Student where cname='"+cname+"')";
		List<Apply> list3 = new ArrayList<Apply>();
		try{
			stmt=con.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next()){
				Apply apply=new Apply();
				apply.setNum(rs.getString("num"));
				list3.add(apply);			
			}
			return list3;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}
	}
	
	//------------��ѧ���ѯ��ٵ�
	public List<Apply> countstu1(){
		String sql="select distinct(num) from Apply where num in(select num from Student)";
		List<Apply> list3 = new ArrayList<Apply>();
		try{
			stmt=con.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next()){
				Apply apply=new Apply();
				apply.setNum(rs.getString("num"));
				list3.add(apply);			
			}
			return list3;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}
	}
	//------------���༶�鿴��ٵ�
	
	public List<Apply> applyByclass(){
		String sql="select distinct(cname) from Apply where cname in(select cname from Student)";
		List<Apply> list3 = new ArrayList<Apply>();
		try{
			stmt=con.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next()){
				Apply apply=new Apply();
				apply.setNum(rs.getString("num"));
				list3.add(apply);			
			}
			return list3;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}
	}
	//��ѯѧ��������
	public List<Apply> count1(String num){
		String sql="select * from Apply where num='"+num+"' and num in(select num from Student where cname='"+cname+"')";
		List<Apply> list4 = new ArrayList<Apply>();
		try{
			stmt=con.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next()){
				Apply apply=new Apply();
				apply.setNum(rs.getString("num"));
				apply.setStatus(rs.getInt("status"));
				apply.setAid(rs.getInt("aid"));
				apply.setDays(rs.getInt("days"));
				list4.add(apply);			
			}
			return list4;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}
	}
	//��ѯѧ��������
	public List<Apply> count(String num){
		String sql="select * from Apply where num='"+num+"'";
		List<Apply> list4 = new ArrayList<Apply>();
		try{
			stmt=con.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next()){
				Apply apply=new Apply();
				apply.setNum(rs.getString("num"));
				apply.setStatus(rs.getInt("status"));
				apply.setAid(rs.getInt("aid"));
				apply.setDays(rs.getInt("days"));
				list4.add(apply);			
			}
			return list4;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}
	}
}
